**********************merge CT600_Fame with Trade data****************

**step1: split the look up table for 1:m merge
*merge lookup take with all traderid
qui use "${hmrc_datadir}\Lookup Tables\FAME lookup", clear
drop vrn_anon
rename turnid traderid
merge m:1 traderid using "${hmrc_datadir}\Trade\trade_id"
tab _merge
unique traderid if _merge==3


keep if _merge==3
drop _merge

*merge with all ct600_id
rename taxpayer_anon identifier
merge m:1 identifier using "${hmrc_datadir}\CT600\ct600_id"
tab _merge
unique id if _merge==3
unique traderid if _merge==3


keep if _merge==3
drop _merge

*save all obs if single company have 1 or more traderid; but each traderid only have 1 unique taxpayerid
duplicates tag traderid, gen (traderid_duplicates)
duplicates tag identifier , gen (taxid_duplicates)
sum traderid_duplicates taxid_duplicates

unique traderid if traderid_duplicates!=0
unique id if traderid_duplicates!=0

preserve
keep if traderid_duplicates ==0
save  "${hmrc_datadir}\Lookup tables\FAME lookup_uniquetraderid.dta" , replace
restore

*save seperately if 1 traderid shared by more than 1 taxpayerid
drop if traderid_duplicates ==0
drop traderid_duplicates taxid_duplicates
save  "${hmrc_datadir}\Lookup tables\FAME lookup_uniquetraderidno.dta" , replace



*step 2: merge trade with splited lookup tables:

use  "${hmrc_datadir}\Lookup tables\FAME lookup_uniquetraderid.dta" ,clear
keep traderid identifier
merge 1:m traderid using "${hmrc_datadir}\Trade\total_collapsed_fy"
/*
preserve
unique traderid if _merge==3
collapse (sum) svalue netmass suppunit, by (flow year _merge)
keep if _merge==3
save "${hmrc_graphdir}\trade_ct600_check\trade of shared traderid by year flow", replace
restore
*/
keep if _merge==3
drop _merge
save "${hmrc_datadir}\Lookup tables\fy lookup_mergetrade1.dta", replace

use "${hmrc_datadir}\Lookup tables\FAME lookup_uniquetraderidno.dta" , clear
keep traderid
duplicates drop
merge 1:m traderid using "${hmrc_datadir}\Trade\total_collapsed_fy"
keep if _merge==3
drop _merge
save "${hmrc_datadir}\Lookup tables\fy sharedtraderid", replace

/*
collapse (sum) svalue netmass suppunit, by (flow year)
gen _merge=0
append using "${hmrc_graphdir}\trade_ct600_check\trade of shared traderid by year flow"
gen shared=(_merge==0)
drop _merge
sort flow year shared
export excel using "${hmrc_graphdir}\trade_ct600_check\trade of shared traderid by year flow.xls", replace
save "${hmrc_graphdir}\trade_ct600_check\trade of shared traderid by year flow", replace
*/

use "${hmrc_datadir}\Lookup tables\FAME lookup_uniquetraderidno.dta" , clear
keep traderid identifier
bysort traderid: gen n=_n
sum n
bysort n: gen x=_n
bysort n: egen y=max(x)
sum y
sum y if n>7
sum y if n>50
drop x y

preserve
keep if n==1
merge 1:m traderid using "${hmrc_datadir}\Lookup tables\fy sharedtraderid"
keep if _merge==3
drop _merge n
save "${hmrc_datadir}\Lookup tables\fy lookup_mergetrade2.dta", replace
restore

forv i=2/7{
preserve
keep if n==`i'
merge 1:m traderid using "${hmrc_datadir}\Lookup tables\fy sharedtraderid"
keep if _merge==3
drop _merge n
append using "${hmrc_datadir}\Lookup tables\fy lookup_mergetrade2.dta"
save "${hmrc_datadir}\Lookup tables\fy lookup_mergetrade2.dta", replace
restore
}

qui preserve
qui keep if n>=8
qui keep traderid
qui duplicates drop
merge 1:m traderid using "${hmrc_datadir}\Lookup tables\fy sharedtraderid"
qui keep if _merge==3
qui drop _merge 
qui save "${hmrc_datadir}\Lookup tables\temp8.dta", replace
qui restore

qui preserve
qui keep if n==8
merge 1:m traderid using "${hmrc_datadir}\Lookup tables\temp8.dta"
qui keep if _merge==3
qui drop _merge n
qui save "${hmrc_datadir}\Lookup tables\fy lookup_mergetrade3.dta", replace
qui restore

forv i=9/20{
preserve
keep if n==`i'
merge 1:m traderid using "${hmrc_datadir}\Lookup tables\temp8"
keep if _merge==3
drop _merge n
append using "${hmrc_datadir}\Lookup tables\fy lookup_mergetrade3.dta"
save "${hmrc_datadir}\Lookup tables\fy lookup_mergetrade3.dta", replace
restore
}

qui preserve
qui keep if n>=21
qui keep traderid
qui duplicates drop
merge 1:m traderid using "${hmrc_datadir}\Lookup tables\fy sharedtraderid"
qui keep if _merge==3
qui drop _merge 
qui save "${hmrc_datadir}\Lookup tables\temp21.dta", replace
erase "${hmrc_datadir}\Lookup tables\temp8.dta"
qui restore

qui preserve
qui keep if n==21
merge 1:m traderid using "${hmrc_datadir}\Lookup tables\temp21.dta"
qui keep if _merge==3
qui drop _merge n
qui save "${hmrc_datadir}\Lookup tables\fy lookup_mergetrade4.dta", replace
qui restore

forv i=22/30{
preserve
keep if n==`i'
merge 1:m traderid using "${hmrc_datadir}\Lookup tables\temp21"
keep if _merge==3
drop _merge n
append using "${hmrc_datadir}\Lookup tables\fy lookup_mergetrade4.dta"
save "${hmrc_datadir}\Lookup tables\fy lookup_mergetrade4.dta", replace
restore
}

qui preserve
qui keep if n==31
merge 1:m traderid using "${hmrc_datadir}\Lookup tables\temp21.dta"
qui keep if _merge==3
qui drop _merge n
qui save "${hmrc_datadir}\Lookup tables\fy lookup_mergetrade5.dta", replace
qui restore

forv i=32/50{
preserve
keep if n==`i'
merge 1:m traderid using "${hmrc_datadir}\Lookup tables\temp21"
keep if _merge==3
drop _merge n
append using "${hmrc_datadir}\Lookup tables\fy lookup_mergetrade5.dta"
save "${hmrc_datadir}\Lookup tables\fy lookup_mergetrade5.dta", replace
restore
}

qui preserve
qui keep if n>=51 
qui keep traderid
qui duplicates drop
merge 1:m traderid using "${hmrc_datadir}\Lookup tables\temp21"
qui keep if _merge==3
qui drop _merge 
qui save "${hmrc_datadir}\Lookup tables\temp51.dta", replace
erase "${hmrc_datadir}\Lookup tables\temp21.dta"
qui restore

qui preserve
qui keep if n==51
merge 1:m traderid using "${hmrc_datadir}\Lookup tables\temp51.dta"
qui keep if _merge==3
qui drop _merge n
qui save "${hmrc_datadir}\Lookup tables\fy lookup_mergetrade6.dta", replace
qui restore

forv i=52/100{
preserve
keep if n==`i'
merge 1:m traderid using "${hmrc_datadir}\Lookup tables\temp51"
keep if _merge==3
drop _merge n
append using "${hmrc_datadir}\Lookup tables\fy lookup_mergetrade6.dta"
save "${hmrc_datadir}\Lookup tables\fy lookup_mergetrade6.dta", replace
restore
}

qui preserve
qui keep if n>=101 
qui keep traderid
qui duplicates drop
merge 1:m traderid using "${hmrc_datadir}\Lookup tables\temp51"
qui keep if _merge==3
qui drop _merge 
qui save "${hmrc_datadir}\Lookup tables\temp101.dta", replace
erase "${hmrc_datadir}\Lookup tables\temp51.dta"
qui restore

qui preserve
qui keep if n==101
merge 1:m traderid using "${hmrc_datadir}\Lookup tables\temp101.dta"
qui keep if _merge==3
qui drop _merge n
qui save "${hmrc_datadir}\Lookup tables\fy lookup_mergetrade7.dta", replace
qui restore

forv i=102/200{
preserve
keep if n==`i'
merge 1:m traderid using "${hmrc_datadir}\Lookup tables\temp101"
keep if _merge==3
drop _merge n
append using "${hmrc_datadir}\Lookup tables\fy lookup_mergetrade7.dta"
save "${hmrc_datadir}\Lookup tables\fy lookup_mergetrade7.dta", replace
restore
}

qui preserve
qui keep if n>=201 
qui keep traderid
qui duplicates drop
merge 1:m traderid using "${hmrc_datadir}\Lookup tables\temp101"
qui keep if _merge==3
qui drop _merge 
qui save "${hmrc_datadir}\Lookup tables\last_479_trader_nobranch.dta", replace
erase "${hmrc_datadir}\Lookup tables\temp101.dta"
qui restore

qui preserve
qui keep if n==201
merge 1:m traderid using "${hmrc_datadir}\Lookup tables\last_479_trader_nobranch.dta"
qui keep if _merge==3
qui drop _merge n
qui save "${hmrc_datadir}\Lookup tables\fy lookup_mergetrade8.dta", replace
qui restore

forv i=202/250{
preserve
keep if n==`i'
merge 1:m traderid using "${hmrc_datadir}\Lookup tables\last_479_trader_nobranch.dta"
keep if _merge==3
drop _merge n
append using "${hmrc_datadir}\Lookup tables\fy lookup_mergetrade8.dta"
save "${hmrc_datadir}\Lookup tables\fy lookup_mergetrade8.dta", replace
restore
}


qui preserve
qui keep if n==251
merge 1:m traderid using "${hmrc_datadir}\Lookup tables\last_479_trader_nobranch.dta"
qui keep if _merge==3
qui drop _merge n
qui save "${hmrc_datadir}\Lookup tables\fy lookup_mergetrade9.dta", replace
qui restore

forv i=251/400{
preserve
keep if n==`i'
merge 1:m traderid using "${hmrc_datadir}\Lookup tables\last_479_trader_nobranch.dta"
keep if _merge==3
drop _merge n
append using "${hmrc_datadir}\Lookup tables\fy lookup_mergetrade9.dta"
save "${hmrc_datadir}\Lookup tables\fy lookup_mergetrade9.dta", replace
restore
}

preserve
keep if n==401
merge 1:m traderid using "${hmrc_datadir}\Lookup tables\last_479_trader_nobranch.dta"
keep if _merge==3
drop _merge n
save "${hmrc_datadir}\Lookup tables\fy lookup_mergetrade10.dta", replace
restore

sum n
local max=r(max)

forv i=401/`max'{
preserve
keep if n==`i'
merge 1:m traderid using "${hmrc_datadir}\Lookup tables\last_479_trader_nobranch.dta"
keep if _merge==3
drop _merge n
append using "${hmrc_datadir}\Lookup tables\fy lookup_mergetrade10.dta"
save "${hmrc_datadir}\Lookup tables\fy lookup_mergetrade10.dta", replace
restore
}

use "${hmrc_datadir}\Lookup tables\fy lookup_mergetrade2.dta", clear
forv i=3/10{
append using "${hmrc_datadir}\Lookup tables\fy lookup_mergetrade`i'.dta"
}
compress
save "${hmrc_datadir}\Lookup tables\fy lookup_mergetrade_sharedid.dta", replace
append using "${hmrc_datadir}\Lookup tables\fy lookup_mergetrade1.dta"
compress
save "${hmrc_datadir}\Lookup tables\fy lookup_mergetrade.dta", replace
******************************merge lookup_trade with CT600_Fame

use "${hmrc_datadir}\Lookup tables\fy lookup_mergetrade.dta", clear
keep identifier
duplicates drop
merge 1:m identifier using "${hmrc_datadir}\CT600\ct600_fame_short.dta"


******************ct600 merge with the trade
qui keep if _merge==3
qui drop _merge

qui merge 1:m year identifier using "${hmrc_datadir}\Lookup tables\fy lookup_mergetrade.dta"
qui keep if _merge==3
qui drop _merge
qui compress
qui save "${hmrc_datadir}\trade_ct600_fy", replace




